﻿#region using
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;
using System.Drawing;
using System.Threading;
using System.Globalization;
using Microsoft.Win32;
using System.Net.Mail;
using System.Data.Sql;
using System.Diagnostics;
#endregion
namespace Turtle8
{

    public class CompanyManager
    {
        #region definitions
        private static Regex regEx = new Regex("[^0-9a-zA-z]");
        private static SqlConnection conn;
        private static Dictionary<string, string> dict;//holds the  mapping between the engWord to hebWord
        public static readonly string StartupPath = System.IO.Directory.GetCurrentDirectory();

        private static string backupPath;
        public static string BackupPath
        { get { return backupPath; } }
        
        
        public static SqlConnection Connection
        { get  {  return conn;} }
        #endregion 

       

        #region Constructors
        public CompanyManager()
        {
            validateSingletonProcess();
            if (getValueFromInitFile("IsInitialized").Equals("false"))
            {
                setDBServerPath();
                initializeDBTables();
                setBackupPath();
            }
            else
            {
                initConnection(false);
                backupPath = getValueFromInitFile("BackupPath");
            }
            
            createEnglishHebrewDictionaryFileFromDataBase();
            createEnglishHebrewDictionaryListFromDatabase();
            createEnglishHebrewListFromFile();
        }
        #endregion

       

        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new MainMenuGUI());
        }

        #region static methods
        static private void createEnglishHebrewDictionaryFileFromDataBase()
        {
            string selectStr = @"SELECT * FROM EnglishHebrewDictionary";

            FileStream fs = new FileStream(@"other\engHebDict.txt", FileMode.Create, FileAccess.ReadWrite);
            StreamWriter sw = new StreamWriter(fs);

            SqlDataAdapter da = new SqlDataAdapter(selectStr, conn);
            SqlDataReader reader = da.SelectCommand.ExecuteReader();

            while (reader.Read())
                sw.WriteLine(reader["engWord"] + "=" + reader["hebWord"]);

            reader.Close();

            // close the stream
            sw.Close();
        }

        /// <summary>
        /// a parser for company.ini initialization file.
        /// </summary>
        /// <param name="key"></param>
        /// <returns>the right value of the key
        ///          example: IsInitialized=false. the function retrurns "false";
        /// </returns>
        static private string getValueFromInitFile(string key)
        {
            // create reader & open file
            TextReader tr = new StreamReader(@"other\company.ini");
            string line;
            string value = string.Empty;

            while ((line = tr.ReadLine()) != null)
                if (line.StartsWith("<" + key))
                {
                    line = line.Substring(line.IndexOf("=") + 1);
                    value += line;

                    while ((!line.EndsWith(">") && (line = tr.ReadLine()) != null))
                        value += line;

                    value = value.Substring(0, value.Length - 1);
                    break;
                }

            tr.Close();
            return value.Trim();
        }


        /// <summary>
        /// performs an INSERT sql operation.
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns>if the cmd performed - true, else false  </returns>
        static public bool insertSqlQuery(SqlCommand sqlCmd)
        {
            sqlCmd.Connection = conn;
            
            int i = sqlCmd.ExecuteNonQuery();
            return true;   
        }

        static public DataTable selectSqlQueryDataTable(SqlCommand sqlCmd)
        {
            sqlCmd.Connection = conn;
            DataTable dt = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
            adapter.Fill(dt);

            return dt;
        }

        static public Int16 selectCountSqlQuery(SqlCommand sqlCmd)
        {
            sqlCmd.Connection = conn;
            return (Convert.ToInt16(sqlCmd.ExecuteScalar()));
        }

        static public void updateSqlNonQuery(string sqlCmd, DataSet ds)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd, conn);
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
            MessageBox.Show(adapter.UpdateCommand.ToString());
        }

        static public void updateSqlNonQuery(SqlCommand sqlCmd)
        {
            sqlCmd.Connection = conn;
            int i = sqlCmd.ExecuteNonQuery();
        }


        /// <summary>
        /// performs a DELETE sql operation.
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns>if there were deleted rows - true, else false</returns>
        static public int deleteSqlNonQuery(SqlCommand sqlCmd)
        {
            sqlCmd.Connection = conn;
            int affected = sqlCmd.ExecuteNonQuery();

            return affected;
        }


        static public bool deleteSoldier(string army_id)
        {
            string table_name = string.Empty;
            string allTablesCmd = @"SELECT * FROM INFORMATION_SCHEMA.TABLES";

            //retrieve the meta data of the tabels in this connection DataBase.
            SqlCommand deleteSqlCmd = new SqlCommand();
            SqlCommand tablesSqlCmd = new SqlCommand(allTablesCmd);

            //defining parameter and setting its values
            DataTable dt = selectSqlQueryDataTable(tablesSqlCmd);
            SqlParameter param = new SqlParameter("@armyID", army_id);
            deleteSqlCmd.Parameters.Add(param);

            //deleting the associated 'armyId' from all tabkes in database
            foreach (DataRow row in dt.Rows)
            {
                try
                {
                    // get the data
                    table_name = (string)row["TABLE_NAME"];
                    deleteSqlCmd.CommandText = @"DELETE FROM " + table_name + " WHERE armyID = @armyID";
                    deleteSqlNonQuery(deleteSqlCmd);

                }
                catch (Exception e)
                {
                    if (!(e.Message.StartsWith("Invalid column name")))
                        MessageBox.Show(e.Message);
                }
            }

            return true;
        }


        /// <summary>
        /// 
        /// </summary>
        static private void populateEnglishHebrewDictionaryTable()
        {
            string sLine = String.Empty;
            string cmd = @"INSERT INTO EnglishHebrewDictionary VALUES(@engWord,@hebWord)";

            FileStream fs = new FileStream(@"other\engHebDict.txt", FileMode.Open, FileAccess.Read);
            StreamReader sr = new StreamReader(fs);

            SqlCommand sqlCmd;
            char[] delimiter = { '=' };
            while ((sLine = sr.ReadLine()) != null)
            {
                string[] pair = sLine.Split(delimiter);
                sqlCmd = new SqlCommand(cmd);
                sqlCmd.Parameters.Add("@engWord", SqlDbType.NVarChar).Value = pair[0].Trim();
                sqlCmd.Parameters.Add("@hebWord", SqlDbType.NVarChar).Value = pair[1].Trim();
                insertSqlQuery(sqlCmd);
            }

            // close the stream
            sr.Close();
        }


        /// <summary>
        /// populates a table of israel cities at first initialization.
        /// </summary>
        static private void populateIsraelCitiesTable()
        {
            string sLine = String.Empty;
            string cmd = @"INSERT INTO IsraelCities (city) VALUES(@City);";

            FileStream fs = new FileStream(@"other\israel-cities.txt", FileMode.Open, FileAccess.Read);
            StreamReader sr = new StreamReader(fs, Encoding.GetEncoding("windows-1255"));

            SqlCommand sqlCmd = new SqlCommand(cmd);

            //inserting the cities into a DataBase
            while ((sLine = sr.ReadLine()) != null)
            {
                sqlCmd = new SqlCommand(cmd);
                sqlCmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = sLine;
                insertSqlQuery(sqlCmd);
            }

            // close the stream
            sr.Close();
        }


        /// <summary>
        /// as its name suggest, this function supposed to be read only if the program is being initialized ( Installation ).
        /// </summary>
        /// <returns></returns>
        private void initializeDBTables()
        {
            initConnection(true);
            
            SqlCommand createDbCmd = new SqlCommand("CREATE DATABASE companyManager", conn);
            createDbCmd.ExecuteNonQuery();
            conn.Close();

            initConnection(false);

            try
            {
                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/IsraelCities.CREATE.sql")));
                populateIsraelCitiesTable();

                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/PersonalData.CREATE.sql")));
                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/ArmyInfo.CREATE.sql")));
                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/ContactInfo.CREATE.sql")));
                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/FamilyInfo.CREATE.sql")));
                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/QualifiedTrack.CREATE.sql")));
                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/TahasukaData.CREATE.sql")));
                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/EnglishHebrewDictionary.CREATE.sql")));
                populateEnglishHebrewDictionaryTable();

                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/TahasukaRequests.CREATE.sql")));
                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/TahasukaZimunim.CREATE.sql")));
                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/TahasukaAlal.CREATE.sql")));
                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/TahasukaSiurKav.CREATE.sql")));
                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/Lekachim.CREATE.sql")));
                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/PracticeData.CREATE.sql")));
                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/PracticeZimunim.CREATE.sql")));
                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/TahasukaPostImages.CREATE.sql")));
                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/TahasukaPosts.CREATE.sql")));
                createSqlTable(new SqlCommand(getSqlStringFromFile(@"/CREATE/Yamam.CREATE.sql")));

                insertSqlQuery(new SqlCommand(getSqlStringFromFile(@"/StoredProcedures/CompanyPakalPanel.SP.medic.sql")));
                insertSqlQuery(new SqlCommand(getSqlStringFromFile(@"/StoredProcedures/CompanyPakalPanel.SP.m203.sql")));
                insertSqlQuery(new SqlCommand(getSqlStringFromFile(@"/StoredProcedures/CompanyPakalPanel.SP.driver.sql")));
                insertSqlQuery(new SqlCommand(getSqlStringFromFile(@"/StoredProcedures/CompanyPakalPanel.SP.heavyGun.sql")));
                insertSqlQuery(new SqlCommand(getSqlStringFromFile(@"/StoredProcedures/CompanyPakalPanel.SP.machine.gun.sql")));
                insertSqlQuery(new SqlCommand(getSqlStringFromFile(@"/StoredProcedures/CompanyPakalPanel.SP.shooter.sql")));
                insertSqlQuery(new SqlCommand(getSqlStringFromFile(@"/StoredProcedures/CompanyPakalPanel.SP.commander.sql")));


                modifyInitFile("IsInitialized", "false", "true");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void initConnection(bool isFirstCall)
        {
            string serverName = getValueFromInitFile("DBServer");
            string connStr;
            if (isFirstCall)
                connStr = "Server=" + serverName + @";Database=master;Trusted_Connection=True;Connection Timeout = 30;";
            else connStr = "Server=" + serverName + @";Database=CompanyManager;Trusted_Connection=True;Connection Timeout = 30;";

            conn = new SqlConnection(connStr);
            conn.Open();
        }



        /// <summary>
        /// returns an array of Israeli cities.
        /// </summary>
        /// <returns></returns>
        static public string[] getCityArray()
        {
            SqlCommand selectCmd = new SqlCommand(@"SELECT city FROM IsraelCities", conn);
            SqlDataReader reader = selectCmd.ExecuteReader();

            List<string> list = new List<string>();

            while (reader.Read())
                list.Add(reader.GetString(0));

            reader.Close();
            string[] arr = list.ToArray();
            return arr;
        }


        /// <summary>
        /// modifies other/company.ini
        /// </summary>
        /// <param name="key"></param>
        /// <param name="oldVal"></param>
        /// <param name="newVal"></param>
        private void modifyInitFile(string key, string oldVal, string newVal)
        {
            // create reader & open file
            TextReader tr = new StreamReader(@"other\company.ini");
            TextWriter tw = new StreamWriter(@"other\company-temp.ini");
            string line;

            while ((line = tr.ReadLine()) != null)
            {
                if (line.StartsWith("<" + key))
                    line = line.Replace(oldVal, newVal);
                tw.WriteLine(line);
            }

            tr.Close();
            tw.Close();

            File.Delete(@"other\company.ini");
            File.Move(@"other\company-temp.ini", @"other\company.ini");
            File.Delete(@"other\company-temp.ini");
        }

        /// <summary>
        /// creates a new database
        /// </summary>
        /// <param name="sqlCmd">holds the SQL CREATE statement</param>
        /// <returns></returns>
        static private void createSqlTable(SqlCommand sqlCmd)
        {
            sqlCmd.Connection = conn;
            sqlCmd.ExecuteNonQuery();
        }


        static private void createEnglishHebrewDictionaryListFromDatabase()
        {
            dict = new Dictionary<string, string>();
            SqlCommand selectCmd = new SqlCommand(@"SELECT * FROM EnglishHebrewDictionary", conn);
            SqlDataReader reader = selectCmd.ExecuteReader();

            while (reader.Read())
                dict.Add((string)reader["engWord"], (string)reader["hebWord"]);

            reader.Close();
        }


        /// <summary>
        /// receives an english word. 
        /// </summary>
        /// <param name="engWord"></param>
        /// <returns>the hebrew string as appears in the data base.</returns>
        static public string translateEnglishWord(string engWord)
        {
            if (engWord == string.Empty)
                return string.Empty;
            return dict[engWord];
        }

        /// <summary>
        /// receives an hebrew word.  
        /// </summary>
        /// <param name="hebWord"></param>
        /// <returns>the english string as appears in the data base.</returns>
        static public string translateHebrewWord(string hebWord)
        {
            Dictionary<string, string>.KeyCollection keys = dict.Keys;

            foreach (string key in keys)
                if (dict[key].Equals(hebWord))
                    return key;
            return string.Empty;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filename"></param>
        /// <returns>the filename as a string</returns>
        static public string getSqlStringFromFile(string filename)
        {
            TextReader tr = new StreamReader(Application.StartupPath + @"\sql" + filename,Encoding.GetEncoding("windows-1255"));

            // reads the whole file and returns it as a string.
            string str = tr.ReadToEnd();

            // close the stream.
            tr.Close();

            return str;
        }



        /// <summary>
        /// gets a collection of Columns. 
        /// sets it's HeaderText property to it's Hebrew display.
        /// </summary>
        /// <param name="collection"></param>
        public static void setTableColumnHeadersHebrew(DataGridViewColumnCollection collection)
        {
            Regex objNaturalPattern = new Regex("[0-9]");
            foreach (DataGridViewColumn item in collection)
            {
                item.HeaderText = objNaturalPattern.Replace(item.HeaderText, string.Empty);
                item.HeaderText = CompanyManager.translateEnglishWord(item.Name);
            }
        }

        public static void setTableColumnHeadersHebrew(DataColumnCollection collection)
        {
            Regex objNaturalPattern = new Regex("[0-9]");
            foreach (DataColumn item in collection)
            {
                item.ColumnName = objNaturalPattern.Replace(item.ColumnName, string.Empty);
                item.ColumnName = CompanyManager.translateEnglishWord(item.ColumnName);
            }
        }

        public static ICollection<string> parseSqlQueryParameters(string cmd)
        {
            ICollection<string> list = new List<string>();

            while (cmd.Contains('@'))
            {
                //truncating the unnecessary prefix of "@......."
                cmd = cmd.Substring(cmd.IndexOf('@') + 1);

                //finds the first occurence  of a non alphabetic symbol
                Match m = regEx.Match(cmd);
                int i = m.Index;
                if (i != 0)
                    list.Add(cmd.Substring(0, i).Trim());
                else list.Add(cmd.Trim());
            }
            return list;
        }


        static public byte[] imageToByteArray(System.Drawing.Image imageIn)
        {
            MemoryStream ms = new MemoryStream();
            imageIn.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);

            return ms.ToArray();
        }

        static public Image byteArrayToImage(byte[] byteArrayIn)
        {
            MemoryStream ms = new MemoryStream(byteArrayIn);
            Image returnImage = Image.FromStream(ms);
            return returnImage;
        }

        private void createEnglishHebrewListFromFile()
        {
            dict = new Dictionary<string, string>();

            string[] file = File.ReadAllLines(Application.StartupPath + @"\other\" + "engHebDict.txt");
            string[] parse = new string[2];


            foreach (string s in file)
            {
                parse = s.Split('=');
                dict.Add(parse[0], parse[1]);
            }
        }
        private void setBackupPath()
        {
            //retreiving the path of MSSQL in file system
            string selectStr = getSqlStringFromFile(@"\other\getInstallationPath.sql");
            SqlCommand selectCmd = new SqlCommand(selectStr, conn);
            SqlDataReader reader = selectCmd.ExecuteReader();
            reader.Read();
            backupPath = (string)reader[0] + @"\Backup";
            reader.Close();

            modifyInitFile("BackupPath", "=", "=" + backupPath);
        }
        private void setDBServerPath()
        {
                string s = System.Environment.MachineName;
                modifyInitFile("DBServer","=", "=" + s + @"\SQLEXPRESS");
        }

        public static SqlParameter createSqlParameter(string paramName, SqlDbType sdt, DataRowVersion drVersion, string columnStr)
        {
            SqlParameter sqlParameter = new SqlParameter(paramName, sdt);
            sqlParameter.SourceVersion = drVersion;
            sqlParameter.SourceColumn = columnStr;

            return sqlParameter;
        }
        #endregion

        /// <summary>
        /// validates that only 1 process instance runs at a time on the same machine.
        /// </summary>
        private void validateSingletonProcess()
        {
            string currentName = Process.GetCurrentProcess().ProcessName;
            Process currentProcess = Process.GetCurrentProcess();
            Array arr = Process.GetProcesses();
            
            foreach (Process item in arr)
            {
                if (item.ProcessName.Equals(currentName) && !item.Id.Equals(currentProcess.Id))
                    currentProcess.Kill();
            }          
            string processName = System.Diagnostics.Process.GetCurrentProcess().ProcessName;

        }

       
    }
}
